package com.SRMS.service;

import com.SRMS.dao.RightToUseStudyRoomDAO;
import com.SRMS.dao.StudentDAO;
import com.SRMS.dao.StudyRoomDAO;
import com.SRMS.domain.RightToUseStudyRoom;
import com.SRMS.domain.Student;
import com.SRMS.domain.StudyRoom;
import com.SRMS.utils.JDBCUtilsByDruid;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

/**
 * @author Albert
 * @version 1.0
 * @date 2023/12/19-20:07
 * @describe 管理员管理学生服务
 */
public class StudentManageService {
    private StudentDAO studentDAO = new StudentDAO();
    private RightToUseStudyRoomDAO rightToUseStudyRoomDAO = new RightToUseStudyRoomDAO();
    private StudyRoomDAO studyRoomDAO = new StudyRoomDAO();

    public List<Student> studentList(){
        return studentDAO.queryMulti("SELECT * FROM student", Student.class);
    }

    /**
     * 删除学生信息
     * @param studentId 学号
     * @return 删除结果（true/false）
     */
    public boolean deleteStudentById(String studentId){
        //如果该学生有申请自习室，撤销学生的使用权
        revokeRightByStudentId(studentId);

        int result = 0;
        String sql1 = "DELETE FROM studyRoomUsageRecord WHERE studentId = ?;";
        String sql3 = "DELETE FROM revokeRecord WHERE studentId = ?;";
        String sql2 = "DELETE FROM student WHERE studentId = ?;";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        //使用事务保证数据一致性
        try {
            connection = JDBCUtilsByDruid.getConnection();
            //将connection设置为不自动提交
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setString(1, studentId);
            preparedStatement.executeUpdate();
            preparedStatement = connection.prepareStatement(sql3);
            preparedStatement.setString(1, studentId);
            result = preparedStatement.executeUpdate();
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setString(1, studentId);
            result = preparedStatement.executeUpdate();

            if(result == 0){
                //撤销回滚
                try {
                    connection.rollback();//默认回滚到事务开始时的状态
                } catch (SQLException ex) {
                    throw new RuntimeException(ex);
                }

                return false;
            }
            //提交事务
            connection.commit();

        } catch (SQLException e) {
            //撤销回滚
            try {
                connection.rollback();//默认回滚到事务开始时的状态
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }

            throw new RuntimeException(e);

        } finally {
            JDBCUtilsByDruid.close(null, preparedStatement, connection);
        }

        return true;
    }

    /**
     * 辅助删除学生方法，根据学号撤销学生申请的自习室，并把自习室的空余座位数增1
     * @param studentId 学号
     * @return 执行结果（true/false）
     */
    public boolean revokeRightByStudentId(String studentId){
        RightToUseStudyRoom rightToUseStudyRoom = getRightByStuId(studentId);
        if(rightToUseStudyRoom == null){
            return false;
        }
        StudyRoom studyRoom = getStudyRoomById(rightToUseStudyRoom.getStudyRoomId());

        int newRemainingSeats = studyRoom.getRemainingSeats() + 1;
        int result1 = 0;
        int result2 = 0;

        String sql1 = "DELETE FROM rightToUseStudyRoom WHERE studentId = ?";
        String sql2 = "UPDATE studyRoom SET remainingSeats = ? WHERE studyRoomId = ?";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        //使用事务保证数据一致性
        try {
            connection = JDBCUtilsByDruid.getConnection();
            //将connection设置为不自动提交
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setString(1, studentId);
            result1 = preparedStatement.executeUpdate();
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setInt(1, newRemainingSeats);
            preparedStatement.setString(2, studyRoom.getStudyRoomId());
            result2 = preparedStatement.executeUpdate();

            if(result1 == 0 || result2 == 0){
                //撤销回滚
                try {
                    connection.rollback();//默认回滚到事务开始时的状态
                } catch (SQLException ex) {
                    throw new RuntimeException(ex);
                }

                return false;
            }
            //提交事务
            connection.commit();

        } catch (SQLException e) {
            //撤销回滚
            try {
                connection.rollback();//默认回滚到事务开始时的状态
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }

            throw new RuntimeException(e);

        } finally {
            JDBCUtilsByDruid.close(null, preparedStatement, connection);
        }

        return true;
    }

    /**
     * 根据自习室编号查询自习室
     * @param studyRoomId
     * @return 返回查询到的自习室
     */
    public StudyRoom getStudyRoomById(String studyRoomId){
        return studyRoomDAO.querySingle("SELECT * FROM studyRoom WHERE studyRoomId=?", StudyRoom.class, studyRoomId);
    }

    /**
     * 根据学号查询自习室使用权
     * @return 返回使用权对象
     */
    public RightToUseStudyRoom getRightByStuId(String studentId){
        return rightToUseStudyRoomDAO.querySingle("SELECT * FROM rightToUseStudyRoom WHERE studentId = ?", RightToUseStudyRoom.class, studentId);
    }

    /**
     * 更新学生信息
     * @param student 学生对象
     * @return 修改结果（true/false）
     */
    public boolean updateStudent(Student student){
        int result = studentDAO.update("UPDATE student SET studentName = ?, password = MD5(?), major = ?, gender = ?, age = ?, telephone = ? WHERE studentId = ?;",
                student.getStudentName(), student.getPassword(), student.getMajor(),
                student.getGender(), student.getAge(), student.getTelephone(), student.getStudentId());
        return result == 1;
    }

    /**
     * 添加学生
     * @param student 需要添加的学生对象
     * @return 添加结果（true/false）
     */
    public boolean addStudent(Student student){
        int result = studentDAO.update("INSERT INTO student VALUES(?,?,MD5(?),?,?,?,?)",
                student.getStudentId(), student.getStudentName(), student.getPassword(), student.getMajor(),
                student.getGender(), student.getAge(), student.getTelephone());
        return result == 1;
    }

    /**
     * 查询学生
     * @param studentId 学号
     * @return 学生对象
     */
    public Student getStudentById(String studentId){
        return studentDAO.querySingle("SELECT * FROM student WHERE studentId = ?", Student.class, studentId);
    }
}
